<?php

namespace App\Http\Controllers\WebApi\Business;


class SpreadSheetBase
{
    private $keyWord = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
        'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];


    //表头
    protected $excelHeader = [['A1', 'col1'], ['B1', 'col2']];

    //数据
    protected $excelData = [['data1', 'data2'], ['data3', 'data4']];

    //添加图片
    protected $excelImgs = [];

    //行高
    protected $rowHeight = 20;

    //单元格宽度
    protected $cellWidth = [];

    //单元格默认宽度
    protected $defaultWidth = 20;

    //表格名称
    protected $excelName = null;

    protected $sheetName = 'sheet';

    protected $styleArray = [
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];

    protected function run()
    {
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();

        //设置sheet的名字  两种方法
//        $sheet->setTitle($this->sheetName);

        $spreadsheet->getActiveSheet()->setTitle($this->sheetName);

        //设置第一行小标题
        foreach ($this->excelHeader as $ak => $v) {
            $sheet->setCellValue($v[0], $v[1]);
            //字体加粗
            $sheet->getStyle($v[0])->getFont()->setBold(true);
            // 垂直居中
            $sheet->getStyle($v[0])->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            $sheet->getStyle($this->keyWord[$ak])->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        }

        // 设置个表格宽度
//        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(16);
//
//        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(80);

        // 垂直居中
//        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
//
//        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
//
//        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
//
//        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

        //设置行高
        $spreadsheet->getActiveSheet()->getRowDimension(1)->setRowHeight($this->rowHeight);
        //表格主体赋值
        foreach ($this->excelData as $k => $v) {
            $num = $k + 2;
            foreach ($v as $key => $val) {
                $sheet->setCellValue($this->keyWord[$key] . $num, $val);
                //设置行宽
                $spreadsheet->getActiveSheet()->getColumnDimension($this->keyWord[$key])->setWidth($this->defaultWidth);
            }
            //设置行高
            $spreadsheet->getActiveSheet()->getRowDimension($num)->setRowHeight($this->rowHeight);
        }

        //指定位置写入图片START
        $this->insertImgs($spreadsheet, $this->excelImgs);
        //指定位置写入图片END

        $excelName = date('Y-m-d', time()) . rand(1000, 9999) . '.xls';

        $excelName = isset($this->excelName) ? $this->excelName : $excelName;

        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment;filename="' . $excelName . '"');

        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');

        $writer->save('php://output');
    }

    //设置表格数据 [['col1','col2']]
    protected function setData($excelData)
    {
        $this->excelData = $excelData;
    }

    //设置表头 array
    protected function setHead($excelHeader)
    {
        if (is_array($excelHeader)) {
            $this->excelHeader = [];
            foreach ($excelHeader as $key => $val) {
                array_push($this->excelHeader, [$this->keyWord[$key] . '1', $val]);
            }
        }
    }

    //设置表名 string
    protected function setName($excelName)
    {
        $this->excelName = $excelName;
    }

    /**
     * 设置图片插入
     * @param array $excelImgs
     * $excelImgs['start']指定图片插入行位置
     * $excelImgs['info']图片数组
     */
    protected function setImgs($excelImgs)
    {
        $this->excelImgs = $excelImgs;
    }

    private function insertImgs($spreadsheet = null, $imgs = [])
    {
        if (!isset($spreadsheet) || empty($imgs)) {
            return false;
        }
        $imgType = isset($imgs['type']) ? $imgs['type'] : 1;//操作类型
        $imgInfo = $imgs['info'];//图片数组路径
        $start = isset($imgs['start']) && is_numeric($imgs['start']) ? $imgs['start'] : count($this->excelData);//第一张图片位置
        $offsetHeight = isset($imgs['offsetHeight']) && is_numeric($imgs['offsetHeight']) ? $imgs['offsetHeight'] : 6;//每张图片垂直偏移量
        $imgTitle = isset($imgs['title']) && is_array($imgs['title']) ? $imgs['title'] : null;//图片标题设置
        $len = count($imgInfo);
        //设置图片区域标题
        $titleStart = $start + ($offsetHeight - 1);
        if ($imgTitle) {
            $ik = 0;
            foreach ($imgTitle as $imgTval) {
                if ($ik === 1) {
                    //空一列
                    $ik = 2;
                }
                //设置标题样式
                $spreadsheet->getActiveSheet()->getStyle($this->keyWord[$ik] . $titleStart)->applyFromArray($this->styleArray);
                //标题字体加粗
                $spreadsheet->getActiveSheet()->getStyle($this->keyWord[$ik] . $titleStart)->getFont()->setBold(true);
                //设置标题内容
                $spreadsheet->getActiveSheet()->setCellValue($this->keyWord[$ik] . $titleStart, $imgTval);
                $ik++;
            }
        }
        for ($i = 0; $i < $len; $i++) {
            $position = ($start + (($i + 1) * $offsetHeight));
            if (file_exists($imgInfo[$i]['url'])) {

                $drawing[$i] = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();

                $drawing[$i]->setName('Logo');

                $drawing[$i]->setDescription('Logo');

                $drawing[$i]->setPath($imgInfo[$i]['url']);

                $drawing[$i]->setWidth(200);

                $drawing[$i]->setHeight(130);

                $drawing[$i]->setCoordinates('A' . $position);

                $drawing[$i]->setOffsetX(4);
//
//                $drawing[$i]->setOffsetY(12);

                $drawing[$i]->setWorksheet($spreadsheet->getActiveSheet());
            }

            //设置与图片关联的其余字段
            if (count($imgInfo[$i]) > 1) {
                $tmp = $imgInfo[$i];
                array_shift($tmp);
                $tk = 2;
                foreach ($tmp as $col) {
                    $spreadsheet->getActiveSheet()->getStyle($this->keyWord[$tk] . ($position + 2))->applyFromArray($this->styleArray);
                    //设置图片区域内容
                    $spreadsheet->getActiveSheet()->setCellValue($this->keyWord[$tk] . ($position + 2), $col);
                    $tk++;
                }
            }
        }
    }
}
